<?php
/**
 * User: sethink
 */

namespace bangtech\swooleOrm\db;

use ErrorException;

class Builder
{
    protected $sethinkBind = [];

    // SQL表达式
    protected $selectSql = 'SELECT%DISTINCT% %FIELD% FROM %TABLE%%ALIAS%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%UNION%%ORDER%%LIMIT%%LOCK%%COMMENT%';

    protected $insertSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';

    protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES %DATA% %COMMENT%';

    protected $updateSql = 'UPDATE %TABLE% SET %SET% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%%COMMENT%';

    protected $deleteSql = 'DELETE FROM %TABLE% %USING% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%%COMMENT%';

    /**
     * @param $options
     * @return array
     * @throws ErrorException
     */
    public function select($options): array
    {
        $sql = str_replace(
            ['%TABLE%', '%ALIAS%', '%DISTINCT%', '%FIELD%', '%JOIN%', '%WHERE%', '%GROUP%', '%HAVING%', '%ORDER%', '%LIMIT%', '%UNION%', '%LOCK%', '%COMMENT%', '%FORCE%'],
            [
                $this->parseTable($options['table']),
                $this->parseAlias($options['alias']),
                $this->parseDistinct($options['distinct']),
                $this->parseField($options['field']),
                $this->parseJoin($options['join']),
                $this->parseWhere($options['where']),
                $this->parseGroup($options['group']),
                $this->parseHaving($options['having']),
                $this->parseOrder($options['order']),
                $this->parseLimit($options['limit']),
                '',
                //$this->parseUnion($options['union']),
                $this->parseLock($options['lock']),
                '',
                //$this->parseComment($options['comment']),
                '',
                //$this->parseForce($options['force']),
            ],
            $this->selectSql);

        return [
            'sql'         => $sql,
            'sethinkBind' => $this->sethinkBind
        ];
    }

    public function insert($options)
    {
        // 分析并处理数据
        if (empty($options['data'])) {
            return '';
        }

        $fields = $values = '';
        foreach ($options['data'] as $k => $v) {
            $fields .= "`{$k}`,";
            $values .= "?,";

            $this->sethinkBind[] = $v;
        }
        $fields = rtrim($fields, ',');
        $values = rtrim($values, ',');

        $sql = str_replace(
            ['%TABLE%', '%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $this->parseTable($options['table']),
                $fields,
                $values,
                ''
                //                    $this->parseComment($options['comment']),
            ],
            $this->insertSql);

        return [
            'sql'         => $sql,
            'sethinkBind' => $this->sethinkBind
        ];
    }

    public function insertAll($options)
    {
        if (empty($options['data'])) {
            return '';
        }

        $keys = [];
        foreach ($options['data'] as $v) {
            $keys = array_merge($keys, array_keys($v));
        }
        $keys = array_merge(array_unique($keys));

        $fields = '';
        foreach ($keys as $v) {
            $fields .= "`{$v}`,";
        }
        $fields = rtrim($fields, ',');

        $data = '';
        foreach ($options['data'] as $v) {
            $data .= '(';
            foreach ($keys as $vv) {
                if (isset($v[$vv])) {
                    $this->sethinkBind[] = $v[$vv];
                } else {
                    $this->sethinkBind[] = '';
                }
                $data .= '?,';
            }
            $data = rtrim($data, ',') . '),';
        }
        $data = rtrim($data, ',');

        $sql = str_replace(
            ['%TABLE%', '%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $this->parseTable($options['table']),
                $fields,
                $data,
                ''
                //                $this->parseComment($options['comment']),
            ],
            $this->insertAllSql);

        return [
            'sql'         => $sql,
            'sethinkBind' => $this->sethinkBind
        ];
    }


    /**
     * @param $options
     * @return array|string
     * @throws ErrorException
     */
    public function update($options)
    {
        if (empty($options['data'])) {
            return '';
        }

        $set = '';
        foreach ($options['data'] as $k => $v) {
            $set                 .= "`{$k}`=?,";
            $this->sethinkBind[] = $v;
        }
        $set = rtrim($set, ',');

        $sql = str_replace(
            ['%TABLE%', '%SET%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($options['table']),
                $set,
                '',
                //                $this->parseJoin($options['join']),
                $this->parseWhere($options['where']),
                $this->parseOrder($options['order']),
                $this->parseLimit($options['limit']),
                $this->parseLock($options['lock']),
                '',
                //                $this->parseComment($options['comment']),
            ],
            $this->updateSql);

        return [
            'sql'         => $sql,
            'sethinkBind' => $this->sethinkBind
        ];
    }

    /**
     * @param $options
     * @return array
     * @throws ErrorException
     */
    public function delete($options): array
    {
        $sql = str_replace(
            ['%TABLE%', '%USING%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($options['table']),
                '',
                //                !empty($options['using']) ? ' USING ' . $this->parseTable($options['using']) . ' ' : '',
                '',
                //                $this->parseJoin($options['join']),
                $this->parseWhere($options['where']),
                $this->parseOrder($options['order']),
                $this->parseLimit($options['limit']),
                $this->parseLock($options['lock']),
                '',
                //                $this->parseComment($options['comment']),
            ],
            $this->deleteSql);
        return [
            'sql'         => $sql,
            'sethinkBind' => $this->sethinkBind
        ];
    }

    /**
     * @param $tableName
     * @return string
     */
    protected function parseTable($tableName): string
    {
        return "`$tableName`";
    }


    /**
     * @param $alias
     * @return string
     */
    protected function parseAlias($alias = ''): string
    {
        if ($alias != ''){
            return " AS {$alias} ";
        }
        return '';
    }

    /**
     * @param $distinct
     * @return string
     */
    protected function parseDistinct($distinct): string
    {
        return !empty($distinct) ? ' DISTINCT ' : '';
    }

    /**
     * @param $order
     * @return string
     */
    protected function parseOrder($order): string
    {
        $orderStr = '';
        foreach ($order as $v) {
            if (is_array($v)) {
                foreach ($v as $kk => $vv) {
                    $orderStr .= "`{$kk}` " . strtoupper($vv) . ',';
                }
            } else {
                $orderStr .= "`{$v}` ASC,";
            }
        }
        $orderStr = rtrim($orderStr, ',');
        return empty($orderStr) ? '' : ' ORDER BY ' . $orderStr;
    }

    /**
     * @param $group
     * @return string
     */
    protected function parseGroup($group): string
    {
        return empty($group) ? '' : " GROUP BY `{$group}`";
    }

    /**
     * @param $having
     * @return string
     */
    protected function parseHaving($having): string
    {
        return empty($having) ? '' : ' HAVING ' . $having;
    }


    /**
     * @param $fields
     * @return string
     */
    protected function parseField($fields): string
    {
        $fieldsStr = '';
        if (is_array($fields) && count($fields) > 0) {
            foreach ($fields as $field) {
                if (strpos($field, '.')) {
                    list($key,$value) = explode('.', $field,2);
                    if ($value  == '*'){
                        $fieldsStr .= "`{$key}`.*,";
                    }else{
                        $fieldsStr .= "`{$key}`.".$this->parseFieldAs($value).",";
                    }
                }else{
                    $fieldsStr .= $key == '*' ?: "`{$key}`,";
                }
            }
            $fieldsStr = rtrim($fieldsStr, ',');
        } elseif (is_string($fields) && preg_match('/^COUNT\((\`)?(\w+|\*)(\`)?\)(\s+)AS(\s+)(\`)?table_count(\`)?$/',$fields)){
            $fieldsStr .= $fields;
        } else {
            $fieldsStr .= '*';
        }
        return $fieldsStr;
    }


    protected function parseFieldAs($field)
    {
        $field = trim($field);
        $field = str_ireplace(' as ',' ',$field);
        if (strpos($field, ' ')) {
            list($key,$alias) = explode(' ',$field,2);
            return "`{$key}` AS `{$alias}`";
        }else{
            return "`{$field}`";
        }
    }


    /**
     * join分析
     * @access protected
     * @param  array $join
     * @return string
     */
    protected function parseJoin(array $join): string
    {
        $joinStr = '';

        foreach ($join as $item) {
            list($table, $type, $on) = $item;

            if (strpos($on, '=')) {
                list($val1, $val2) = explode('=', $on, 2);
                $condition =  $this->parseJoinField($val1)."=".$this->parseJoinField($val2);
            } else {
                $condition = $on;
            }

            $table = $this->parseFieldAs($table);
            $joinStr .= " {$type} JOIN {$table} ON {$condition}";
        }

        return $joinStr;
    }

    protected function parseJoinField($field)
    {
        $field = trim($field);
        if (strpos($field, '.')) {
            list($key,$value) = explode('.', $field,2);
            if ($value  == '*'){
                return  "`{$key}`.*";
            }else{
                return  "`{$key}`.".$this->parseFieldAs($value);
            }
        }else{
            return $field == '*' ?: "`{$field}`";
        }
    }

    /**
     * @param $k
     * @param $v
     * @return false|string
     * @throws ErrorException
     */
    protected function whereExp($k, $v)
    {
        if(array_key_exists(0,$v)){
            $v[0] = strtoupper($v[0]);

            switch ($v[0]) {
                case '=':
                case '<>':
                case '>':
                case '>=':
                case '<':
                case '<=':
                case 'LIKE':
                case 'NOT LIKE':
                    return $this->parseCompare($k, $v);
                case 'IN':
                case 'NOT IN':
                    return $this->parseIn($k, $v);
            }
        }else{
            throw new ErrorException('There is no key value of 0');
        }
        return false;
    }

    /**
     * @param $where
     * @return string
     * @throws ErrorException
     */
    protected function parseWhere($where): string
    {
        $whereStr = '';
        foreach ($where as $v) {

            foreach ($v as $kk => $vv) {

                if (is_array($vv)) {
                    if (count($vv) == 3 && strtoupper($vv[2]) == 'OR') {

                        $whereStr = rtrim($whereStr, " AND ") . ' OR ';
                    }
                    $whereStr .= $this->whereExp($kk, $vv);
                } else {
                    $whereStr            .= "(`{$kk}` = ?)";
                    $this->sethinkBind[] = $vv;
                }

                $whereStr .= ' AND ';
            }
        }
        $whereStr = rtrim($whereStr, " AND ");
        return empty($whereStr) ? '' : ' WHERE ' . $whereStr;
    }


    /**
     * @param $k
     * @param $v
     * @return string
     */
    protected function parseCompare($k, $v): string
    {
        $whereStr            = "(`{$k}` {$v[0]} ?)";
        $this->sethinkBind[] = $v[1];
        return $whereStr;
    }

    /**
     * @param $k
     * @param $v
     * @return string
     */
    protected function parseIn($k, $v): string
    {
        $whereStr = '';

        $value_tmp = '';
        foreach ($v[1] as $vv) {
            $this->sethinkBind[] = $vv;
            $value_tmp           .= "?,";
        }
        if (strlen($value_tmp) > 0) {
            $value_tmp = rtrim($value_tmp, ',');
            $value     = "($value_tmp)";
            $whereStr  .= "(`{$k}` {$v[0]} {$value})";
        }

        return $whereStr;
    }

    /**
     * @param $limit
     * @return string
     */
    protected function parseLimit($limit): string
    {
        return (!empty($limit) && false === strpos($limit, '(')) ? ' LIMIT ' . $limit . ' ' : '';
    }


    /**
     * @param false $lock
     * @return string
     */
    protected function parseLock($lock = false): string
    {
        if (is_bool($lock)) {
            return $lock ? ' FOR UPDATE ' : '';
        } elseif (is_string($lock) && !empty($lock)) {
            return ' ' . trim($lock) . ' ';
        }
        return '';
    }


    public function __destruct()
    {
        unset($this->sethinkBind);
    }


}
